package com.csdj.dao.impl;

import com.csdj.dao.BaseDao;
import com.csdj.dao.EmployeeDao;
import com.csdj.entity.Department;
import com.csdj.entity.Employee;
import com.csdj.entity.Positions;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * packageName com.csdj.dao.impl
 *
 * @author winver
 * @version 1.0
 * @className EmployeeDaoImpl
 * @date 2024/11/27
 * @description TODO
 */

public class EmployeeDaoImpl extends BaseDao implements EmployeeDao {


    @Override
    public Employee getEmployeeByName(String name) {
        //1. 定义返回对象
        Employee employee = null;
        //2.获取连接
        conn = this.getConnection();
        //3.写sql
        String sql = "select empid,name,password,department_id,position_id,status,img from sys_employee" +
                "  where name = ?";
        try {
            //4.预编译sql
            psmt = conn.prepareStatement(sql);
            //5.设置占位符
            psmt.setString(1, name);
            //6.执行SQL
            rs = psmt.executeQuery();
            //7.解析结果集
            while (rs.next()) {
                employee = new Employee();
                employee.setEmpid(rs.getInt("empid"));
                employee.setName(rs.getString(2));
                employee.setPassword(rs.getString(3));
                employee.setDepartmentId(rs.getInt(4));
                employee.setPositionId(rs.getInt(5));
                employee.setStatus(rs.getString(6));
                employee.setImg(rs.getString(7));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //释放资源
            this.closeAll(conn, psmt, rs);
        }
        return employee;
    }

    @Override
    public Employee getEmployeeInfoByName(String name) {
        //1. 定义返回对象
        Employee employee = null;
        //2.获取连接
        conn = this.getConnection();
        //3.写sql
        String sql = "SELECT emp.empid,emp.name,emp.password,emp.department_id,emp.position_id,emp.`status`," +
                "  dept.depteid,dept.`name`,dept.manager_sn,pos.positionsid,pos.name_cn,pos.name_en,emp.img,emp.email FROM `sys_employee`  emp " +
                "    inner join sys_department dept " +
                " on (emp.department_id = dept.depteid) " +
                " inner join sys_positions  pos " +
                " on (emp.position_id = pos.positionsid) " +
                " where emp.`name` = ?";
        try {
            //4.预编译sql
            rs = this.executeQuery(sql, name);
            //7.解析结果集
            while (rs.next()) {
                employee = new Employee();
                employee.setEmpid(rs.getInt(1));
                employee.setName(rs.getString(2));
                employee.setPassword(rs.getString(3));
                employee.setDepartmentId(rs.getInt(4));
                employee.setPositionId(rs.getInt(5));
                employee.setStatus(rs.getString(6));
                employee.setImg(rs.getString(13));
                employee.setEmail(rs.getString(14));

                //部门信息
                Department dept = new Department(rs.getInt(7),
                        rs.getString(8),
                        rs.getInt(9));

                //岗位信息
                Positions positions = new Positions(rs.getInt(10),
                        rs.getString(11),
                        rs.getString(12));

                //将部门及岗位放到员工对象
                employee.setDepartment(dept);
                employee.setPositions(positions);

            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //释放资源
            this.closeAll(conn, psmt, rs);
        }
        return employee;
    }

    @Override
    public List<Employee> getEmpsByPage(int startIndex, int pageSize, String name, int depteId) {
        //1. 返回对象
        List<Employee> employeeList = new ArrayList<>();
        //2.打开连接
        this.conn = this.getConnection();
        //3.准备SQL
        String sql = "SELECT emp.empid,emp.name,emp.password,emp.department_id," +
                " emp.position_id,emp.`status`, dept.depteid,dept.`name`," +
                " dept.manager_sn,pos.positionsid,pos.name_cn,pos.name_en,emp.img,emp.email" +
                " FROM `sys_employee`  emp    inner join sys_department dept " +
                " on (emp.department_id = dept.depteid)  inner join " +
                " sys_positions  pos  on (emp.position_id = pos.positionsid) " +
                " where  emp.name like concat('%',?,'%')";
        //多参可能变化的参数时，怎么让代码简洁一些
        if (depteId != -1) {
            sql += "  and dept.depteid = ?";
        }
        sql += " order by emp.empid desc  limit ?,?";
        try {
            //4.预编译SQL
            if (depteId != -1) {
                rs = this.executeQuery(sql, name, depteId, startIndex, pageSize);
            } else {
                rs = this.executeQuery(sql, name, startIndex, pageSize);
            }
            //7.解析结果集
            while (rs.next()) {
                Employee employee = new Employee();
                employee.setEmpid(rs.getInt(1));
                employee.setName(rs.getString(2));
                employee.setPassword(rs.getString(3));
                employee.setDepartmentId(rs.getInt(4));
                employee.setPositionId(rs.getInt(5));
                employee.setStatus(rs.getString(6));
                employee.setImg(rs.getString(13));
                employee.setEmail(rs.getString(14));

                //部门信息
                Department dept = new Department(rs.getInt(7),
                        rs.getString(8),
                        rs.getInt(9));

                //岗位信息
                Positions positions = new Positions(rs.getInt(10),
                        rs.getString(11),
                        rs.getString(12));

                //将部门及岗位放到员工对象
                employee.setDepartment(dept);
                employee.setPositions(positions);

                //放入集合
                employeeList.add(employee);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //释放资源
            this.closeAll(conn, psmt, rs);
        }
        return employeeList;
    }

    @Override
    public int getEmpsPageCount(String name, int depteId) {
        int count = 0;
        this.conn = this.getConnection();
        String sql = "SELECT count(1) cnt FROM `sys_employee`  emp   " +
                "  inner join sys_department dept on " +
                " (emp.department_id = dept.depteid)  inner join " +
                " sys_positions  pos  on (emp.position_id = pos.positionsid)" +
                " where  emp.name like concat('%',?,'%')";
        try {
            if (depteId != -1) {
                sql += "  and dept.depteid = ?";
                rs = this.executeQuery(sql, name, depteId);
            } else {
                rs = this.executeQuery(sql, name);
            }
            if (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeAll(conn, psmt, rs);
        }
        return count;
    }

    @Override
    public int addEmp(Employee employee) {
        this.conn = this.getConnection();
        String sql = "insert into sys_employee(name,password,department_id,position_id,status,img,email)values(?,?,?,1,?,?,?)";
        try {
            return executeUpdate(sql, employee.getName(), employee.getPassword(), employee.getDepartmentId(), employee.getStatus(), employee.getImg(), employee.getEmail());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeAll(conn, psmt, rs);
        }
    }

    @Override
    public int getEmail(String email) {
        int count = 0;
        this.conn = this.getConnection();
        String sql = "select count(*) from`sys_employee` where email = ?";
        try {
            rs = executeQuery(sql, email);
            if (rs.next()){
                count= rs.getInt(1);
            }
            return count;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            closeAll(conn, psmt, rs);
        }
    }

    @Override
    public List<Employee> getEmployeeInfoList(String name) {
        //1.返回对象
        List<Employee> employeeList = new ArrayList<>();
        //2连接对象
        this.conn= this.getConnection();
        //3.编写sql
        String sql ="select empid,name,password,department_id,position_id,status,img from sys_employee" +
                "  where name like concat('%',?,'%')";
        //4.预编译SQL
        try {
           rs= this.executeQuery(sql,name);
            while (rs.next()){
                Employee employee = new Employee();
                employee.setEmpid(rs.getInt(1));
                employee.setName(rs.getString(2));
                employee.setPassword(rs.getString(3));
                employee.setDepartmentId(rs.getInt(4));
                employee.setPositionId(rs.getInt(5));
                employee.setStatus(rs.getString(6));
                employee.setImg(rs.getString(7));
                employeeList.add(employee);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            closeAll(conn,psmt,rs);
        }

        return employeeList;
    }
}
